package com.mexcad.db.connectors;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.mexcad.db.dto.CatEstado;
import com.mexcad.db.dto.Cliente;
import com.mexcad.db.dto.Gerente;
import com.mexcad.db.dto.Usuario;
import com.mexcad.db.utils.ExceptionDB;
import com.mexcad.db.utils.HibernateUtil;

public class GerenteConnector {

 public static final int GERENTE_SOBREESCRITO=1;
 public static final int GERENTE_ASIGNADO=2;
 public static final int GERENTE_ELIMINADO=3;
 
	
 private static Log logger = LogFactory.getLog(GerenteConnector.class);

	public GerenteConnector(){
		logger.debug("Instanciating GerenteConnector");
	}
	
	
	
	
/*	public static Afectaciones creaInstanciaAfectaciones(){
/		Afectaciones afect = new Afectaciones();
		return afect;
	}
	*/
	public static List<Gerente> getAllGerentes(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		List <Gerente>lista=null;
		try{
			logger.debug("Querying Gerente");
			String query ="FROM Gerente";
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		
		return lista;	
	}

	public static Gerente getGerentePorCliente(int idCliente){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Gerente gerente=null;
		try{
			logger.debug("Querying Gerente");
			String query =" FROM  Gerente where ckGerente.idCliente="+idCliente;
			 gerente= (Gerente)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return gerente;
	}
	
	public static Gerente getGerentePorId(int idGerente){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Gerente gerente= null;
		try{
			logger.debug("Querying Gerente");
			String query =" FROM  Gerente where idGerente="+idGerente;
			 gerente= (Gerente)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return gerente;
	}
	
	
	public static synchronized int guardaGerenteActualizaCliente(Gerente gerente){
		Session session =null;
		Transaction transaction = null;
		
		try{
			 session = HibernateUtil.getSessionFactory().openSession();
			 transaction = session.beginTransaction();
			
			gerente.setNombres(gerente.getNombres().toUpperCase());
			gerente.setApellidoPaterno(gerente.getApellidoPaterno().toUpperCase());
			gerente.setApellidoMaterno(gerente.getApellidoMaterno().toUpperCase());
			gerente.setRfc(gerente.getRfc().toUpperCase());
			

				
			logger.debug("Querying Gerente");
			if(gerente.getCkGerente().getIdGerente() == 0){
				if(!ClienteConnector.isGerenteAssignedById(gerente.getCkGerente().getIdCliente())){
					gerente.getCkGerente().setIdGerente(getUltimoIdGerente()+1);
				}else{
					gerente.getCkGerente().setIdGerente(getGerenteByCliente(gerente.getCkGerente().getIdCliente()).getCkGerente().getIdGerente());
				}
			}
			session.saveOrUpdate(gerente);
			transaction.commit();
			session.close();

			Cliente cliente = ClienteConnector.getClienteById(gerente.getCkGerente().getIdCliente());
			cliente.setIdGerente(gerente.getCkGerente().getIdGerente());
			ClienteConnector.guardaCliente(cliente);			
			
			
			return gerente.getCkGerente().getIdGerente();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			transaction.rollback();
			if(session!= null && session.isOpen())
				session.close();
			return ExceptionDB.getNumMessageDB(excepts);
		}catch(Exception e){
			transaction.rollback();
			if(session!= null && session.isOpen())
				session.close();
			return ExceptionDB.getNumMessageDB(e);
		}finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		
	}
	
	public static synchronized int guardaGerente(Gerente gerente){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction transaction = session.beginTransaction();
		
		try{

			gerente.setNombres(gerente.getNombres().toUpperCase());
			gerente.setApellidoPaterno(gerente.getApellidoPaterno().toUpperCase());
			gerente.setApellidoMaterno(gerente.getApellidoMaterno().toUpperCase());
			gerente.setRfc(gerente.getRfc().toUpperCase());
			
			logger.debug("Querying Gerente");
			if(gerente.getCkGerente().getIdGerente() == 0){
				gerente.getCkGerente().setIdGerente(getUltimoIdGerente()+1);
			}
			session.saveOrUpdate(gerente);
			transaction.commit();
			session.close();
	
			return gerente.getCkGerente().getIdGerente();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			transaction.rollback();
			if(session!= null && session.isOpen())
				session.close();
			
			return ExceptionDB.getNumMessageDB(excepts);
		}catch(Exception e){
			transaction.rollback();
			if(session!= null && session.isOpen())
				session.close();
			
			return ExceptionDB.getNumMessageDB(e);
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		
	}

	
	

    public static Gerente getGerenteByCliente(int idCliente){
    	Session session = HibernateUtil.getSessionFactory().openSession();
    	Gerente gerente =null;
		try{
    		String query = " from Gerente where idCliente="+idCliente;
		
			gerente = (Gerente) session.createQuery(query).uniqueResult();
			session.close();
	    }catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null && session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null && session.isOpen())
				session.close();
			
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return gerente;
    	
    }


	public static   int getUltimoIdGerente(){
		Integer max =null;
		Session session = HibernateUtil.getSessionFactory().openSession();
		
		try{
			String query = "select max(ckGerente.idGerente) from Gerente ";
		
			max = (Integer) session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null && session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null && session.isOpen())
				session.close();
			
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		if(max==null)
			return 0;
		else
			return max;
		
	}
	
	public static synchronized void eliminaGerente(int idGerente) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction transaction = session.beginTransaction();
		
		try{
			
			Gerente gerente= getGerentePorId(idGerente);
	
			session.delete(gerente);
			transaction.commit();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null && session.isOpen())
				session.close();
			
		}catch(Exception e){
			if(session!= null && session.isOpen())
				session.close();
			
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		session.close();
	}	
	
	/*
	public static CtCvesProgoPerAnualNER getCtCvesProgoPerAnualByProgNER(CtClavesPoaPK pkAfec,String re, String rs, String actividad){
		logger.debug("Querying CtCvesProgoPerAnual");
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CtCvesProgoPerAnualNER where anio="+pkAfec.getAnioId()+" AND "+
						"sector='"+pkAfec.getSectorId()+"' AND  "+
						"subsector='"+pkAfec.getSubsectorId()+"' AND  "+
						"unidadr='"+pkAfec.getUnidadId()+"' AND  "+
						"re='"+re+"' AND  "+
						"sr='"+rs+"' AND " +
						"ai='"+actividad+"'" ;
		
		CtCvesProgoPerAnualNER clavePOA=(CtCvesProgoPerAnualNER)session.createQuery(query).uniqueResult();
		session.close();	
		
		return clavePOA;
	}



   public static List<AfectacionesStatus> getAllAfectacionesStatus(){
			logger.debug("Querying AfectacionesStatus");
			Session session = HibernateUtil.getSessionFactory().openSession();
			String query ="FROM AfectacionesStatus";
			List <AfectacionesStatus>lista=session.createQuery(query).list();
			return lista;
   }
   
*/
	public static void main(String []arg){
		
		//List <Cliente>lista2=ClienteConnector.getClientesPorEstado(1);
	//	System.out.println("parentCount: "+GerenteConnector.getStateCount());
		
		
	}
	
}
